package nl.jjsoft.airlock.controller
{
	import flash.data.SQLMode;
	import flash.filesystem.File;
	import flash.utils.ByteArray;
	
	import mx.collections.ArrayCollection;
	
	import nl.jjsoft.airlock.AirLockConstants;
	import nl.jjsoft.airlock.SqlDB;
	import nl.jjsoft.airlock.domain.Model;
	import nl.jjsoft.airlock.domain.Session;
	import nl.jjsoft.airlock.utils.PasswordGenerator;
	import nl.jjsoft.airlock.vo.AccountVo;
	import nl.jjsoft.airlock.vo.CredentialsVO;
	import nl.jjsoft.airlock.vo.GroupVO;
	
	public class SafeController implements ISafeController
	{
		[Inject]
		public var model:Model;
		
		[Inject]
		public var session:Session;
		
		private var _db:SqlDB = null;		
		private function get db():SqlDB {
			if (_db == null) {
				var dbFile:File = new File(session.dbFilePath);
				_db = new SqlDB();
				var key:ByteArray = PasswordGenerator.getEncryptionKey(session.safeCombination);
				_db.connect(dbFile, key);
			}
			return _db;
		}
		
		public function SafeController() { 	}

		public function createPasswordDB(dbPath:String, safeCombination:String):void {
			var dbFile:File = new File(dbPath);
			var key:ByteArray = PasswordGenerator.getEncryptionKey(safeCombination);
			var db:SqlDB = new SqlDB();
			db.connect(dbFile, key, SQLMode.CREATE);
			
			var query:String = 'CREATE TABLE IF NOT EXISTS [Groups] ('+
				' [id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT, '+			
				' [name] VARCHAR(30)  NOT NULL'+			
				' )';
			db.execute(query);	
					
			query = 'CREATE TABLE IF NOT EXISTS [Passwords] (' +
				' [id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,'+
				' [group_id] INTEGER  NOT NULL,'+
				' [name] VARCHAR(30)  NOT NULL,'+
				' [url] VARCHAR(200)  NULL,'+
				' [notes] TEXT  NULL,'+
				' [user_name] VARCHAR(100)  NULL,'+
				' [user_pass] NVARCHAR(100)  NULL'+
				' )';
			db.execute(query);
			
			query = 'CREATE TABLE IF NOT EXISTS [PasswordPolicy] (' + 
					'[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,' +
					'[length] INTEGER NOT NULL,' + 
					'[useLowerCase] INTEGER NOT NULL,' + 
					'[useUpperCase] INTEGER NOT NULL,' + 
					'[useSymbols] INTEGER NOT NULL,' + 
					'[useDigits] INTEGER NOT NULL)';
			db.execute(query);
			
			query = 'CREATE TABLE IF NOT EXISTS [DBVersion] (' + 
					'[version] VARCHAR(30) NOT NULL)';					
			db.execute(query);
			
			query = 'INSERT INTO [DBVersion]' +
					'([version]) VALUES(:version)';
			var params:Object = new Object();
			params[":version"] = AirLockConstants.DB_VERSION;
			db.execute(query, params);
			
			db.disconnect();					
		}		
		
		public function getAppVersionFromDB():String {
			var version:String = null;
			var query:String = 'SELECT [version] FROM [DBVersion]';
			try {
				var result:Array = db.select(query);
				if (result.length == 1)
					version = result[0]['version'].toString();
			}	
			catch (error:Error){ 
				// do nothing
			}		
			return version;
		}

		public function getGroups():Array {
			var query:String = 'SELECT * FROM Groups';
			return db.select(query, null, GroupVO);
		}
		
		public function getAccountsForGroup(group:GroupVO):ArrayCollection {
			// make sure group id is not null
			// TODO EXCEPTION?
			if (isNaN(group.id)){ return null; }
			
			var query:String = 'SELECT * FROM Passwords' +
				' WHERE group_id=:group_id';
			var params:Object = new Object();
			params[":group_id"] = group.id;
			return new ArrayCollection(db.select(query, params, AccountVo));			
		}
		
		public function getCredentialsForPassword(password:AccountVo):CredentialsVO {
			// make sure group id is not null
			// TODO EXCEPTION?
			if (isNaN(password.id)){ return null; }
			
			var query:String = 'SELECT user_name, user_pass FROM Passwords' +
				' WHERE id=:id';
			var params:Object = new Object();
			params[":id"] = password.id;
			var arr:Array = db.select(query, params, AccountVo);
			if (arr != null) {
				return arr[0] as CredentialsVO;
			}
			else {
				return null;
			}						
		}
		
		public function saveGroup(group:GroupVO):void {
			var query:String = '';
			var params:Object = new Object();
			params[":name"] = group.name;
			
			// perform an update or a insert?
			if (isNaN(group.id)) { // INSERT
				// check if the groupname doesn't exist already
				// TODO Jamie Put unique contraint on group.name column
//				query = 'SELECT id FROM Groups WHERE name=:name';						
//				if (db.select(query, params, GroupVO) != null)	{
//					return false;
//				}
				// still here lets insert the group
				query = 'INSERT INTO Groups ([name]) VALUES(:name)';
				group.id = db.insert(query, params);
			}
			else { // UPDATE (known group id)
				query = 'UPDATE Groups' +
					' SET name=:name' +
					' WHERE id=:id';
				params[":id"] = group.id;
				if (!db.update(query, params)) {
					trace("An error occured when updating the group with id [" + group.name + "]");
				}
			}
		}
				
		public function deleteGroup(group:GroupVO):Boolean {
			// make sure group id is not null
			// TODO EXCEPTION?
			if (isNaN(group.id)){ return false; }
			
			var query:String = 'DELETE FROM Groups' +
				' WHERE id=:id';
			var params:Object = new Object();
			params[":id"] = group.id;
			return db.remove(query, params);
		}
		
		public function saveAccount(password:AccountVo):Number {
			// make sure group id is not null
			// TODO EXCEPTION?			
			if (isNaN(password.group_id)){ return -1; }
			
			password.appendHttpToUrlIfNotExists();
			var query:String = '';
			var params:Object = new Object();
			params[":name"] = password.name;
			params[":group_id"] = password.group_id;
			
			// TODO check here if GROUP EXISTS		
			
			// perform an update or a insert?			
			if (isNaN(password.id)){
				// check if the passwordname doesn't exist already in the group
				query = 'SELECT id FROM Passwords WHERE name=:name' + 
					' AND group_id=:group_id';
				if (db.select(query, params, AccountVo)!= null){
					return -1;
				}
				
				// still here let's insert the password
				query = 'INSERT INTO Passwords([group_id],[name],[url],[notes],[user_name],[user_pass])' +
					' VALUES(:group_id,:name,:url,:notes,:user_name,:user_pass)'; 
				params[":url"] = password.url;
				params[":notes"] = password.notes;
				params[":user_name"] = password.credentials.user_name;
				params[":user_pass"] = password.credentials.user_pass;
				return db.insert(query, params);
			}
			else { // UPDATE (known password id)		
				query = 'UPDATE Passwords' +
					' SET name=:name, group_id=:group_id, url=:url,' +
					' notes=:notes, user_name=:user_name, user_pass=:user_pass' +
					' WHERE id=:id';
				params[":id"] = password.id;
				params[":url"] = password.url;
				params[":notes"] = password.notes;
				params[":user_name"] = password.credentials.user_name;
				params[":user_pass"] = password.credentials.user_pass;				
				return db.update(query, params);
			}
		}
		
		public function deleteAccount(password:AccountVo):Boolean{
			// make sure password id is not null
			// TODO EXCEPTION?						
			if (isNaN(password.id)){ return false; }
			
			var query:String = 'DELETE FROM Passwords' +
				' WHERE id=:id';
			var params:Object = new Object();
			params[":id"] = password.id;
			return db.remove(query, params);
		}	
	}
}